----------------------------------------------------------------------------
-- drop table
----------------------------------------------------------------------------
drop table indi_ticketing;
drop table indi_seat;
drop table indi_schedule_time;
drop table indi_schedule_date;
drop table indi_showing;
drop table indi_cinema;
drop table indi_award;
drop table indi_movie;
drop table indi_director;
drop table indi_notice;
drop table indi_magazine_board;
drop table indi_production_comment;
drop table indi_production_board;
drop table indi_movieforum_comment;
drop table indi_movieforum_board;
drop table indi_member;
drop table indi_admin;
drop table indi_point_level_range;

-- 2차 개발 예정
drop table indi_rate;
----------------------------------------------------------------------------
-- create sequence
----------------------------------------------------------------------------
create sequence movieforum_board_no_seq;
create sequence movieforum_comment_no_seq;
create sequence production_board_no_seq;
create sequence production_comment_no_seq;
create sequence magazine_board_no_seq;
create sequence notice_board_no_seq;
create sequence director_no_seq;
create sequence movie_no_seq;
create sequence award_no_seq;
create sequence cinema_no_seq;
create sequence showing_no_seq;
create sequence schedule_date_no_seq;
create sequence schedule_time_no_seq;
create sequence seat_no_seq;
create sequence ticketing_no_seq;

delete from indi_movieforum_comment;

select*from indi_movieforum_comment;
-- 2차 개발 예정

create sequence rate_no_seq;
----------------------------------------------------------------------------
-- drop sequence 
----------------------------------------------------------------------------
drop sequence ticketing_no_seq;
drop sequence seat_no_seq;
drop sequence schedule_time_no_seq;
drop sequence schedule_date_no_seq;
drop sequence showing_no_seq;
drop sequence cinema_no_seq;
drop sequence award_no_seq;
drop sequence movie_no_seq;
drop sequence director_no_seq;
drop sequence notice_board_no_seq;
drop sequence magazine_board_no_seq;
drop sequence production_comment_no_seq;
drop sequence production_board_no_seq;
drop sequence movieforum_comment_no_seq;
drop sequence movieforum_board_no_seq;


-- 2차 개발 예정

drop sequence rate_no_seq;
----------------------------------------------------------------------------
-- 필수 insert 문
----------------------------------------------------------------------------
-- 회원 등록
insert into indi_member(member_email,password,nick,register_date) values('javaking','1234','자바킹',sysdate);
-- 관리자 등록
insert into indi_admin(admin_email,password,nick,grade) values('manager','1234','관리자',0);
-- 영화관 등록
insert into indi_cinema(cinema_no, name, lat, lng) values(cinema_no_seq.nextval, 'CGV 압구정', '37.524414', '127.0291085');
insert into indi_cinema(cinema_no, name, lat, lng) values(cinema_no_seq.nextval, 'CGV 왕십리', '37.561873', '127.0291085');
insert into indi_cinema(cinema_no, name, lat, lng) values(cinema_no_seq.nextval, 'CGV 강남', '37.501564', '127.026313');
insert into indi_cinema(cinema_no, name, lat, lng) values(cinema_no_seq.nextval, 'CGV 명동', '37.563341', '126.982858');
-- 레벨 결정범위 등록
insert into indi_point_level_range(grade, lowpoint, hipoint) values('1','0','200');
insert into indi_point_level_range(grade, lowpoint, hipoint) values('2','201','400');
insert into indi_point_level_range(grade, lowpoint, hipoint) values('3','401','600');
insert into indi_point_level_range(grade, lowpoint, hipoint) values('4','601','800');
insert into indi_point_level_range(grade, lowpoint, hipoint) values('5','801','1000');
----------------------------------------------------------------------------
-- create table
----------------------------------------------------------------------------
-- 회원 & 관리자
----------------------------------------------------------------------------
-- 관리자 테이블
create table indi_admin(
   admin_email varchar2(100) primary key,
   password varchar2(100) not null,
   nick varchar2(100) not null,
   grade number default 0   
);
-- 회원 테이블
create table indi_member(
   member_email varchar2(100) primary key,
   password varchar2(100) not null,
   nick varchar2(100) not null,
   point number default 0,
   register_date date not null
);
----------------------------------------------------------------------------
-- 게시판
----------------------------------------------------------------------------
-- 무비포럼 테이블
create table indi_movieforum_board(
   board_no number primary key,
   title varchar2(100) not null,   
   content varchar2(4000) not null,
   hits number default 0,
   posted_date date not null,
   img_path varchar2(100),
   video_path varchar2(100),
   writer varchar2(100) not null
   constraint fk_writer_movieforum references indi_member(member_email)
);
-- 무비포럼 댓글 테이블
create table indi_movieforum_comment(
   comment_no number primary key,
   writer varchar2(100) not null,
   content varchar2(2000) not null,
   posted_date date not null,
   board_no number null
   constraint fk_board_no_movieforum references indi_movieforum_board(board_no)
);
-- 우리들이 만든 영화 테이블
create table indi_production_board(
   board_no number primary key,
   title varchar2(100) not null,   
   content varchar2(4000) not null,
   hits number default 0,
   posted_date date not null,
   img_path varchar2(100),
   video_path varchar2(100),
   writer varchar2(100) not null
   constraint fk_writer_production references indi_member(member_email)
);
-- 우리들이 만든 영화 댓글 테이블
create table indi_production_comment(
   comment_no number primary key,
   writer varchar2(100) not null,
   content varchar2(2000) not null,
   posted_date date not null,
   board_no number null
   constraint fk_board_no_production references indi_production_board(board_no) on delete cascade
);
-- 매거진 테이블
create table indi_magazine_board(
   board_no number primary key,
   title varchar2(100) not null,   
   content varchar2(4000) not null,
   hits number default 0,
   posted_date date not null,
   img_path varchar2(100),
   video_path varchar2(100),
   writer varchar2(100) not null
   constraint fk_writer_magazine references indi_admin(admin_email)
);
-- 공지사항 테이블
create table indi_notice(
   board_no number primary key,
   title varchar2(100) not null,   
   content varchar2(4000) not null,
   hits number default 0,
   posted_date date not null,
   img_path varchar2(100),
   video_path varchar2(100),
   writer varchar2(100) not null
   constraint fk_writer_notice references indi_admin(admin_email)
);
----------------------------------------------------------------------------
-- 감독 & 영화 & 상영 & 예매
----------------------------------------------------------------------------
-- 감독정보 테이블
create table indi_director(
   director_no number primary key,
   name varchar2(100) not null,
   directorImgPath varchar2(100) not null
);
-- 영화정보 테이블
create table indi_movie(
   movie_no number primary key,
   title varchar2(100) not null,
   genre varchar2(100) not null,
   story varchar2(4000) not null,
   poster varchar2(100),
   trailer varchar2(100),
   director_no number not null 
   constraint fk_director_no_movie references indi_director(director_no)
);
-- 수상정보 테이블
create table indi_award(
   award_no number primary key,
   info varchar2(2000) not null,
   movie_no number not null
   constraint fk_movie_no_award references indi_movie(movie_no)
);
-- 영화관 테이블 
create table indi_cinema(
   cinema_no number primary key,
   name varchar2(100) not null,
   lat varchar2(100) not null,
   lng varchar2(100) not null
);
-- 현재 상영 중 영화 테이블
create table indi_showing(
   showing_no number primary key,
   showing_start date not null,
   showing_end date not null,
   movie_no number not null
   constraint fk_movie_no_showing references indi_movie(movie_no),
   cinema_no number not null
   constraint fk_cinema_no_showing references indi_cinema(cinema_no)
);
-- 스케쥴 일자 테이블
create table indi_schedule_date(
   date_no number primary key,
   schedule_date date not null,
   showing_no number not null
   constraint fk_showing_no references indi_showing(showing_no)
);
-- 스케쥴 시간 관련 테이블
create table indi_schedule_time(
   time_no number primary key,
   schedule_time number not null,
   date_no number not null
   constraint fk_date_no references indi_schedule_date(date_no)
);
-- 좌석 테이블
create table indi_seat(
   seat_no number primary key,
   info varchar2(4000) not null,
   time_no number not null
   constraint fk_time_no references indi_schedule_time(time_no)
);
-- 예매정보 테이블
create table indi_ticketing(
   ticketing_no number primary key,   
   ticketing_seat varchar2(4000) not null,
   adult_count number not null,
   stu_count number not null,
   member_email varchar2(100) not null
   constraint fk_member_email_ticketing references indi_member(member_email),
   time_no number not null
   constraint fk_time_no_ticketing references indi_schedule_time(time_no)
);
-- 레벨 결정 범위 테이블
create table indi_point_level_range(
   grade number primary key,
   lowpoint number not null,
   hipoint number not null   
);

-- 2차 개발

-- 영화 한줄평 테이블
create table indi_rate(
   rate_no number primary key,   
   content varchar2(2000) not null,
   score number not null,
   posted_date date not null,   
   writer varchar2(100) not null
   constraint fk_writer_rate references indi_member(member_email),
   movie_no number not null
   constraint fk_movie_no_rate references indi_movie(movie_no)
);
select to_char(d.schedule_date,'YYYY.MM.DD') as schedule_date, t.schedule_time from indi_schedule_date d, indi_schedule_time t where showing_no=5 and d.date_no = t.date_no;
select * from indi_showing
select * from INDI_SCHEDULE_DATE d, indi_showing s, indi_cinema c where d.showing_no=s.showing_no and c.cinema_no = 1 and s.cinema_no = c.cinema_no;
select * from INDI_SCHEDULE_time;
--반드시 수정 요망
select to_char(d.schedule_date,'YYYY.MM.DD') as schedule_date, t.schedule_time from INDI_SCHEDULE_DATE d, indi_showing s, indi_cinema c, INDI_SCHEDULE_time t 
where c.cinema_no = 3 and c.cinema_no = s.cinema_no and d.showing_no=s.showing_no and d.date_no = t.date_no;

